{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import _pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  \\\n",
       "0  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                        song_id source_system_tab  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=           explore   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=        my library   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=        my library   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=        my library   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=           explore   \n",
       "\n",
       "    source_screen_name      source_type  target  \n",
       "0              Explore  online-playlist       1  \n",
       "1  Local playlist more   local-playlist       1  \n",
       "2  Local playlist more   local-playlist       1  \n",
       "3  Local playlist more   local-playlist       1  \n",
       "4              Explore  online-playlist       1  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train = pd.read_csv('train.csv')\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=</td>\n",
       "      <td>WmHKgKMlp1lQMecNdNvDMkvIycZYHnFwDT72I5sIssc=</td>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-library</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=</td>\n",
       "      <td>y/rsZ9DC7FwK5F2PK2D5mj+aOBUJAjuu3dZ14NgE0vM=</td>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-library</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>/uQAlrAkaczV+nWCd2sPF2ekvXPRipV7q0l+gbLuxjw=</td>\n",
       "      <td>8eZLFOdGVdXBSqoAv5nsLigeH2BvKXzTQYtUM53I0k4=</td>\n",
       "      <td>discover</td>\n",
       "      <td>NaN</td>\n",
       "      <td>song-based-playlist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>ztCf8thYsS4YN3GcIL/bvoxLm/T5mYBVKOO4C9NiVfQ=</td>\n",
       "      <td>radio</td>\n",
       "      <td>Radio</td>\n",
       "      <td>radio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>MKVMpslKcQhMaFEgcEQhEfi5+RZhMYlU3eRDpySrH8Y=</td>\n",
       "      <td>radio</td>\n",
       "      <td>Radio</td>\n",
       "      <td>radio</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                          msno  \\\n",
       "0   0  V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=   \n",
       "1   1  V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=   \n",
       "2   2  /uQAlrAkaczV+nWCd2sPF2ekvXPRipV7q0l+gbLuxjw=   \n",
       "3   3  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "4   4  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "\n",
       "                                        song_id source_system_tab  \\\n",
       "0  WmHKgKMlp1lQMecNdNvDMkvIycZYHnFwDT72I5sIssc=        my library   \n",
       "1  y/rsZ9DC7FwK5F2PK2D5mj+aOBUJAjuu3dZ14NgE0vM=        my library   \n",
       "2  8eZLFOdGVdXBSqoAv5nsLigeH2BvKXzTQYtUM53I0k4=          discover   \n",
       "3  ztCf8thYsS4YN3GcIL/bvoxLm/T5mYBVKOO4C9NiVfQ=             radio   \n",
       "4  MKVMpslKcQhMaFEgcEQhEfi5+RZhMYlU3eRDpySrH8Y=             radio   \n",
       "\n",
       "    source_screen_name          source_type  \n",
       "0  Local playlist more        local-library  \n",
       "1  Local playlist more        local-library  \n",
       "2                  NaN  song-based-playlist  \n",
       "3                Radio                radio  \n",
       "4                Radio                radio  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.read_csv('test.csv')\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>song_length_s</th>\n",
       "      <th>song_length_s_log</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=</td>\n",
       "      <td>247640</td>\n",
       "      <td>465</td>\n",
       "      <td>張信哲 (Jeff Chang)</td>\n",
       "      <td>董貞</td>\n",
       "      <td>何啟弘</td>\n",
       "      <td>3.0</td>\n",
       "      <td>247</td>\n",
       "      <td>5.513429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=</td>\n",
       "      <td>197328</td>\n",
       "      <td>444</td>\n",
       "      <td>BLACKPINK</td>\n",
       "      <td>TEDDY|  FUTURE BOUNCE|  Bekuh BOOM</td>\n",
       "      <td>TEDDY</td>\n",
       "      <td>31.0</td>\n",
       "      <td>197</td>\n",
       "      <td>5.288267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=</td>\n",
       "      <td>231781</td>\n",
       "      <td>465</td>\n",
       "      <td>SUPER JUNIOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>231</td>\n",
       "      <td>5.446737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=</td>\n",
       "      <td>273554</td>\n",
       "      <td>465</td>\n",
       "      <td>S.H.E</td>\n",
       "      <td>湯小康</td>\n",
       "      <td>徐世珍</td>\n",
       "      <td>3.0</td>\n",
       "      <td>273</td>\n",
       "      <td>5.613128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=</td>\n",
       "      <td>140329</td>\n",
       "      <td>726</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>52.0</td>\n",
       "      <td>140</td>\n",
       "      <td>4.948760</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id  song_length genre_ids  \\\n",
       "0  CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=       247640       465   \n",
       "1  o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=       197328       444   \n",
       "2  DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=       231781       465   \n",
       "3  dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=       273554       465   \n",
       "4  W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=       140329       726   \n",
       "\n",
       "        artist_name                            composer     lyricist  \\\n",
       "0  張信哲 (Jeff Chang)                                  董貞          何啟弘   \n",
       "1         BLACKPINK  TEDDY|  FUTURE BOUNCE|  Bekuh BOOM        TEDDY   \n",
       "2      SUPER JUNIOR                                 NaN          NaN   \n",
       "3             S.H.E                                 湯小康          徐世珍   \n",
       "4              貴族精選                         Traditional  Traditional   \n",
       "\n",
       "   language  song_length_s  song_length_s_log  \n",
       "0       3.0            247           5.513429  \n",
       "1      31.0            197           5.288267  \n",
       "2      31.0            231           5.446737  \n",
       "3       3.0            273           5.613128  \n",
       "4      52.0            140           4.948760  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "songs = pd.read_csv('FE_songs.csv')\n",
    "songs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "song_id                    0\n",
       "song_length                0\n",
       "genre_ids              94116\n",
       "artist_name                0\n",
       "composer             1071354\n",
       "lyricist             1945268\n",
       "language                   0\n",
       "song_length_s              0\n",
       "song_length_s_log          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "songs.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "songs = songs.drop(['song_length_s_log','song_length'],axis =1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "song_id 2296320\n",
      "genre_ids 1046\n",
      "artist_name 222363\n",
      "composer 329824\n",
      "lyricist 110926\n",
      "language 10\n",
      "song_length_s 3264\n"
     ]
    }
   ],
   "source": [
    "for col in songs.columns:\n",
    "    print(col,len(songs[col].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>registration_init_time</th>\n",
       "      <th>expiration_date</th>\n",
       "      <th>expiration_date_year</th>\n",
       "      <th>expiration_date_month</th>\n",
       "      <th>expiration_date_Ym</th>\n",
       "      <th>use_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>XQxgAYj3klVKjR3oxPPXYYFp4soD4TuBghkhMTD4oTw=</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>unknown</td>\n",
       "      <td>7</td>\n",
       "      <td>20110820</td>\n",
       "      <td>20170920</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>201709</td>\n",
       "      <td>2223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>UizsfmJb9mV54qE9hCYyU07Va97c0lCRLEQX3ae+ztM=</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>unknown</td>\n",
       "      <td>7</td>\n",
       "      <td>20150628</td>\n",
       "      <td>20170622</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>201706</td>\n",
       "      <td>725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D8nEhsIOBSoE6VthTaqDX8U6lqjJ7dLdr72mOyLya2A=</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>unknown</td>\n",
       "      <td>4</td>\n",
       "      <td>20160411</td>\n",
       "      <td>20170712</td>\n",
       "      <td>2017</td>\n",
       "      <td>7</td>\n",
       "      <td>201707</td>\n",
       "      <td>457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>mCuD+tZ1hERA/o5GPqk38e041J8ZsBaLcu7nGoIIvhI=</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>unknown</td>\n",
       "      <td>9</td>\n",
       "      <td>20150906</td>\n",
       "      <td>20150907</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>201509</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>q4HRBfVSssAFS9iRfxWrohxuk9kCYMKjHOEagUMV6rQ=</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>unknown</td>\n",
       "      <td>4</td>\n",
       "      <td>20170126</td>\n",
       "      <td>20170613</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>201706</td>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  city  bd   gender  \\\n",
       "0  XQxgAYj3klVKjR3oxPPXYYFp4soD4TuBghkhMTD4oTw=     1 NaN  unknown   \n",
       "1  UizsfmJb9mV54qE9hCYyU07Va97c0lCRLEQX3ae+ztM=     1 NaN  unknown   \n",
       "2  D8nEhsIOBSoE6VthTaqDX8U6lqjJ7dLdr72mOyLya2A=     1 NaN  unknown   \n",
       "3  mCuD+tZ1hERA/o5GPqk38e041J8ZsBaLcu7nGoIIvhI=     1 NaN  unknown   \n",
       "4  q4HRBfVSssAFS9iRfxWrohxuk9kCYMKjHOEagUMV6rQ=     1 NaN  unknown   \n",
       "\n",
       "   registered_via  registration_init_time  expiration_date  \\\n",
       "0               7                20110820         20170920   \n",
       "1               7                20150628         20170622   \n",
       "2               4                20160411         20170712   \n",
       "3               9                20150906         20150907   \n",
       "4               4                20170126         20170613   \n",
       "\n",
       "   expiration_date_year  expiration_date_month  expiration_date_Ym  use_days  \n",
       "0                  2017                      9              201709      2223  \n",
       "1                  2017                      6              201706       725  \n",
       "2                  2017                      7              201707       457  \n",
       "3                  2015                      9              201509         1  \n",
       "4                  2017                      6              201706       138  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "members = pd.read_csv('FE_members.csv')\n",
    "members.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "msno 34403\n",
      "city 21\n",
      "bd 73\n",
      "gender 3\n",
      "registered_via 6\n",
      "expiration_date_year 17\n",
      "expiration_date_month 12\n",
      "expiration_date_Ym 137\n",
      "use_days 4345\n"
     ]
    }
   ],
   "source": [
    "members = members.drop(['registration_init_time','expiration_date'],axis =1)\n",
    "for col in members.columns:\n",
    "    print(col,len(members[col].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "msno                       0\n",
       "song_id                    0\n",
       "source_system_tab      24849\n",
       "source_screen_name    414804\n",
       "source_type            21539\n",
       "target                     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                         0\n",
       "msno                       0\n",
       "song_id                    0\n",
       "source_system_tab       8442\n",
       "source_screen_name    162883\n",
       "source_type             7297\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 表合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "d:\\python37\\lib\\site-packages\\ipykernel_launcher.py:3: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                          msno  \\\n",
       "0 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                        song_id source   source_screen_name  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=  train              Explore   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=  train  Local playlist more   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=  train  Local playlist more   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=  train  Local playlist more   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=  train              Explore   \n",
       "\n",
       "  source_system_tab      source_type  target  \n",
       "0           explore  online-playlist     1.0  \n",
       "1        my library   local-playlist     1.0  \n",
       "2        my library   local-playlist     1.0  \n",
       "3        my library   local-playlist     1.0  \n",
       "4           explore  online-playlist     1.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['source'] = 'train'\n",
    "test['source'] = 'test'\n",
    "data = pd.concat([train,test],ignore_index = False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>...</th>\n",
       "      <th>expiration_date_year</th>\n",
       "      <th>expiration_date_month</th>\n",
       "      <th>expiration_date_Ym</th>\n",
       "      <th>use_days</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>song_length_s</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>201710</td>\n",
       "      <td>2103</td>\n",
       "      <td>359</td>\n",
       "      <td>Bastille</td>\n",
       "      <td>Dan Smith| Mark Crew</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>206.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>201709</td>\n",
       "      <td>2301</td>\n",
       "      <td>1259</td>\n",
       "      <td>Various Artists</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>284.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>201709</td>\n",
       "      <td>2301</td>\n",
       "      <td>1259</td>\n",
       "      <td>Nas</td>\n",
       "      <td>N. Jones、W. Adams、J. Lordan、D. Ingle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>225.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>201709</td>\n",
       "      <td>2301</td>\n",
       "      <td>1019</td>\n",
       "      <td>Soundway</td>\n",
       "      <td>Kwadwo Donkoh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>255.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>201710</td>\n",
       "      <td>2103</td>\n",
       "      <td>1011</td>\n",
       "      <td>Brett Young</td>\n",
       "      <td>Brett Young| Kelly Archer| Justin Ebach</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>187.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                          msno  \\\n",
       "0 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                        song_id source   source_screen_name  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=  train              Explore   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=  train  Local playlist more   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=  train  Local playlist more   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=  train  Local playlist more   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=  train              Explore   \n",
       "\n",
       "  source_system_tab      source_type  target  city    bd  ...  \\\n",
       "0           explore  online-playlist     1.0     1   NaN  ...   \n",
       "1        my library   local-playlist     1.0    13  24.0  ...   \n",
       "2        my library   local-playlist     1.0    13  24.0  ...   \n",
       "3        my library   local-playlist     1.0    13  24.0  ...   \n",
       "4           explore  online-playlist     1.0     1   NaN  ...   \n",
       "\n",
       "  expiration_date_year  expiration_date_month  expiration_date_Ym  use_days  \\\n",
       "0                 2017                     10              201710      2103   \n",
       "1                 2017                      9              201709      2301   \n",
       "2                 2017                      9              201709      2301   \n",
       "3                 2017                      9              201709      2301   \n",
       "4                 2017                     10              201710      2103   \n",
       "\n",
       "   genre_ids      artist_name                                 composer  \\\n",
       "0        359         Bastille                     Dan Smith| Mark Crew   \n",
       "1       1259  Various Artists                                      NaN   \n",
       "2       1259              Nas     N. Jones、W. Adams、J. Lordan、D. Ingle   \n",
       "3       1019         Soundway                            Kwadwo Donkoh   \n",
       "4       1011      Brett Young  Brett Young| Kelly Archer| Justin Ebach   \n",
       "\n",
       "  lyricist language song_length_s  \n",
       "0      NaN     52.0         206.0  \n",
       "1      NaN     52.0         284.0  \n",
       "2      NaN     52.0         225.0  \n",
       "3      NaN     -1.0         255.0  \n",
       "4      NaN     52.0         187.0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.merge(data,members,how='left',on='msno')\n",
    "data = pd.merge(data,songs,how='left',on='song_id')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                       7377418\n",
       "msno                           0\n",
       "song_id                        0\n",
       "source                         0\n",
       "source_screen_name        577687\n",
       "source_system_tab          33291\n",
       "source_type                28836\n",
       "target                   2556790\n",
       "city                           0\n",
       "bd                       3994195\n",
       "gender                         0\n",
       "registered_via                 0\n",
       "expiration_date_year           0\n",
       "expiration_date_month          0\n",
       "expiration_date_Ym             0\n",
       "use_days                       0\n",
       "genre_ids                 160565\n",
       "artist_name                  139\n",
       "composer                 2295010\n",
       "lyricist                 4403542\n",
       "language                     139\n",
       "song_length_s                139\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                       7377418\n",
       "msno                           0\n",
       "song_id                        0\n",
       "source                         0\n",
       "source_screen_name        577687\n",
       "source_system_tab          33291\n",
       "source_type                28836\n",
       "target                   2556790\n",
       "city                           0\n",
       "bd                             0\n",
       "gender                         0\n",
       "registered_via                 0\n",
       "expiration_date_year           0\n",
       "expiration_date_month          0\n",
       "expiration_date_Ym             0\n",
       "use_days                       0\n",
       "genre_ids                 160565\n",
       "artist_name                  139\n",
       "language                     139\n",
       "song_length_s                139\n",
       "dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.drop(['composer','lyricist'],axis =1,inplace =True)\n",
    "data.bd.fillna(data.bd.median(),inplace = True)\n",
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>target</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>expiration_date_year</th>\n",
       "      <th>expiration_date_month</th>\n",
       "      <th>expiration_date_Ym</th>\n",
       "      <th>use_days</th>\n",
       "      <th>language</th>\n",
       "      <th>song_length_s</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.556790e+06</td>\n",
       "      <td>7.377418e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934069e+06</td>\n",
       "      <td>9.934069e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.278394e+06</td>\n",
       "      <td>5.035171e-01</td>\n",
       "      <td>7.488166e+00</td>\n",
       "      <td>2.805023e+01</td>\n",
       "      <td>6.792155e+00</td>\n",
       "      <td>2.017077e+03</td>\n",
       "      <td>8.326120e+00</td>\n",
       "      <td>2.017160e+05</td>\n",
       "      <td>1.620287e+03</td>\n",
       "      <td>1.895934e+01</td>\n",
       "      <td>2.442900e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>7.380818e+05</td>\n",
       "      <td>4.999877e-01</td>\n",
       "      <td>6.648934e+00</td>\n",
       "      <td>6.698503e+00</td>\n",
       "      <td>2.273409e+00</td>\n",
       "      <td>3.898629e-01</td>\n",
       "      <td>2.495316e+00</td>\n",
       "      <td>3.786038e+01</td>\n",
       "      <td>1.133356e+03</td>\n",
       "      <td>2.129982e+01</td>\n",
       "      <td>6.894602e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>2.004000e+03</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>2.004100e+05</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6.391972e+05</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>2.600000e+01</td>\n",
       "      <td>4.000000e+00</td>\n",
       "      <td>2.017000e+03</td>\n",
       "      <td>9.000000e+00</td>\n",
       "      <td>2.017090e+05</td>\n",
       "      <td>7.000000e+02</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>2.140000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.278394e+06</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>5.000000e+00</td>\n",
       "      <td>2.700000e+01</td>\n",
       "      <td>7.000000e+00</td>\n",
       "      <td>2.017000e+03</td>\n",
       "      <td>9.000000e+00</td>\n",
       "      <td>2.017090e+05</td>\n",
       "      <td>1.430000e+03</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>2.400000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.917592e+06</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.300000e+01</td>\n",
       "      <td>2.900000e+01</td>\n",
       "      <td>9.000000e+00</td>\n",
       "      <td>2.017000e+03</td>\n",
       "      <td>1.000000e+01</td>\n",
       "      <td>2.017100e+05</td>\n",
       "      <td>2.284000e+03</td>\n",
       "      <td>5.200000e+01</td>\n",
       "      <td>2.710000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.556789e+06</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>2.200000e+01</td>\n",
       "      <td>8.700000e+01</td>\n",
       "      <td>1.600000e+01</td>\n",
       "      <td>2.020000e+03</td>\n",
       "      <td>1.200000e+01</td>\n",
       "      <td>2.020100e+05</td>\n",
       "      <td>5.149000e+03</td>\n",
       "      <td>5.900000e+01</td>\n",
       "      <td>1.085100e+04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id        target          city            bd  registered_via  \\\n",
       "count  2.556790e+06  7.377418e+06  9.934208e+06  9.934208e+06    9.934208e+06   \n",
       "mean   1.278394e+06  5.035171e-01  7.488166e+00  2.805023e+01    6.792155e+00   \n",
       "std    7.380818e+05  4.999877e-01  6.648934e+00  6.698503e+00    2.273409e+00   \n",
       "min    0.000000e+00  0.000000e+00  1.000000e+00  3.000000e+00    3.000000e+00   \n",
       "25%    6.391972e+05  0.000000e+00  1.000000e+00  2.600000e+01    4.000000e+00   \n",
       "50%    1.278394e+06  1.000000e+00  5.000000e+00  2.700000e+01    7.000000e+00   \n",
       "75%    1.917592e+06  1.000000e+00  1.300000e+01  2.900000e+01    9.000000e+00   \n",
       "max    2.556789e+06  1.000000e+00  2.200000e+01  8.700000e+01    1.600000e+01   \n",
       "\n",
       "       expiration_date_year  expiration_date_month  expiration_date_Ym  \\\n",
       "count          9.934208e+06           9.934208e+06        9.934208e+06   \n",
       "mean           2.017077e+03           8.326120e+00        2.017160e+05   \n",
       "std            3.898629e-01           2.495316e+00        3.786038e+01   \n",
       "min            2.004000e+03           1.000000e+00        2.004100e+05   \n",
       "25%            2.017000e+03           9.000000e+00        2.017090e+05   \n",
       "50%            2.017000e+03           9.000000e+00        2.017090e+05   \n",
       "75%            2.017000e+03           1.000000e+01        2.017100e+05   \n",
       "max            2.020000e+03           1.200000e+01        2.020100e+05   \n",
       "\n",
       "           use_days      language  song_length_s  \n",
       "count  9.934208e+06  9.934069e+06   9.934069e+06  \n",
       "mean   1.620287e+03  1.895934e+01   2.442900e+02  \n",
       "std    1.133356e+03  2.129982e+01   6.894602e+01  \n",
       "min    0.000000e+00 -1.000000e+00   1.000000e+00  \n",
       "25%    7.000000e+02  3.000000e+00   2.140000e+02  \n",
       "50%    1.430000e+03  3.000000e+00   2.400000e+02  \n",
       "75%    2.284000e+03  5.200000e+01   2.710000e+02  \n",
       "max    5.149000e+03  5.900000e+01   1.085100e+04  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id 2556791\n",
      "msno 34403\n",
      "song_id 419839\n",
      "source 2\n",
      "source_screen_name 23\n",
      "source_system_tab 9\n",
      "source_type 13\n",
      "target 3\n",
      "city 21\n",
      "bd 72\n",
      "gender 3\n",
      "registered_via 6\n",
      "expiration_date_year 17\n",
      "expiration_date_month 12\n",
      "expiration_date_Ym 137\n",
      "use_days 4345\n",
      "genre_ids 609\n",
      "artist_name 46373\n",
      "language 11\n",
      "song_length_s 1926\n"
     ]
    }
   ],
   "source": [
    "for col in data.columns:\n",
    "    print(col,len(data[col].unique()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "特别特征  5： id, msno, song_id, source, target\n",
    "类别特征 12：source_screen_name，source_system_tab，source_type, city, gender, registered_via, expiration_date_year, expiration_date_month, expiration_date_Ym, genre_ids, language,artist_name\n",
    "数值特征  3：song_length_s ,bd, use_days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'msno', 'song_id', 'source', 'source_screen_name',\n",
       "       'source_system_tab', 'source_type', 'target', 'city', 'bd', 'gender',\n",
       "       'registered_via', 'expiration_date_year', 'expiration_date_month',\n",
       "       'expiration_date_Ym', 'use_days', 'genre_ids', 'artist_name',\n",
       "       'language', 'song_length_s'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "source_screen_name属性有23的不同取值，各取值及其出现的次数\n",
      "\n",
      "Local playlist more     4073317\n",
      "Online playlist more    1824496\n",
      "Radio                    685668\n",
      "Album more               596285\n",
      "Search                   420469\n",
      "Artist more              363428\n",
      "Discover Feature         337647\n",
      "Discover Chart           292657\n",
      "Others profile more      292252\n",
      "Discover Genre           123819\n",
      "My library               101539\n",
      "Explore                  100214\n",
      "Unknown                   77790\n",
      "Discover New              21232\n",
      "Search Trends             18515\n",
      "Search Home               18187\n",
      "My library_Search          8565\n",
      "Self profile more           343\n",
      "Concert                      60\n",
      "Payment                      24\n",
      "People local                 13\n",
      "People global                 1\n",
      "Name: source_screen_name, dtype: int64\n",
      "\n",
      "source_system_tab属性有9的不同取值，各取值及其出现的次数\n",
      "\n",
      "my library      4704222\n",
      "discover        3050320\n",
      "search           900901\n",
      "radio            689466\n",
      "listen with      310894\n",
      "explore          233972\n",
      "notification       8309\n",
      "settings           2833\n",
      "Name: source_system_tab, dtype: int64\n",
      "\n",
      "source_type属性有13的不同取值，各取值及其出现的次数\n",
      "\n",
      "local-library             2843745\n",
      "online-playlist           2742456\n",
      "local-playlist            1374040\n",
      "radio                      698273\n",
      "album                      672534\n",
      "top-hits-for-artist        602974\n",
      "song                       373875\n",
      "song-based-playlist        297706\n",
      "listen-with                277341\n",
      "topic-article-playlist      16276\n",
      "artist                       3466\n",
      "my-daily-playlist            2686\n",
      "Name: source_type, dtype: int64\n",
      "\n",
      "city属性有21的不同取值，各取值及其出现的次数\n",
      "\n",
      "1     3557272\n",
      "13    1522929\n",
      "5     1108657\n",
      "4      734087\n",
      "15     644267\n",
      "22     626326\n",
      "6      375456\n",
      "14     319988\n",
      "12     198076\n",
      "9      125307\n",
      "8      113945\n",
      "18     107657\n",
      "11     100966\n",
      "10      95298\n",
      "21      87886\n",
      "3       83045\n",
      "17      62137\n",
      "7       42750\n",
      "16      10896\n",
      "19       9311\n",
      "20       7952\n",
      "Name: city, dtype: int64\n",
      "\n",
      "gender属性有3的不同取值，各取值及其出现的次数\n",
      "\n",
      "unknown    4013703\n",
      "male       3066075\n",
      "female     2854430\n",
      "Name: gender, dtype: int64\n",
      "\n",
      "registered_via属性有6的不同取值，各取值及其出现的次数\n",
      "\n",
      "9     3769646\n",
      "7     3452943\n",
      "3     1669740\n",
      "4     1019599\n",
      "13      22278\n",
      "16          2\n",
      "Name: registered_via, dtype: int64\n",
      "\n",
      "expiration_date_year属性有17的不同取值，各取值及其出现的次数\n",
      "\n",
      "2017    8689096\n",
      "2018    1012531\n",
      "2016     203570\n",
      "2015      12610\n",
      "2019       8717\n",
      "2013       2047\n",
      "2014       1636\n",
      "2012       1103\n",
      "2020        850\n",
      "2008        516\n",
      "2011        434\n",
      "2007        360\n",
      "2004        227\n",
      "2006        187\n",
      "2005        142\n",
      "2010        106\n",
      "2009         76\n",
      "Name: expiration_date_year, dtype: int64\n",
      "\n",
      "expiration_date_month属性有12的不同取值，各取值及其出现的次数\n",
      "\n",
      "9     4548678\n",
      "10    2306300\n",
      "8      398714\n",
      "7      393867\n",
      "6      369843\n",
      "1      336247\n",
      "12     329813\n",
      "2      325973\n",
      "11     280444\n",
      "3      256288\n",
      "5      194605\n",
      "4      193436\n",
      "Name: expiration_date_month, dtype: int64\n",
      "\n",
      "expiration_date_Ym属性有137的不同取值，各取值及其出现的次数\n",
      "\n",
      "201709    4468271\n",
      "201710    2249334\n",
      "201708     330996\n",
      "201707     319622\n",
      "201706     277200\n",
      "201711     242703\n",
      "201712     209139\n",
      "201801     190516\n",
      "201702     160501\n",
      "201802     160419\n",
      "201701     141965\n",
      "201803     131337\n",
      "201703     119032\n",
      "201612     115417\n",
      "201804     109664\n",
      "201805      98639\n",
      "201705      92153\n",
      "201806      82731\n",
      "201704      78180\n",
      "201807      70730\n",
      "201809      67346\n",
      "201808      58606\n",
      "201810      40783\n",
      "201611      33503\n",
      "201610      12887\n",
      "201609      11696\n",
      "201608       7421\n",
      "201606       6343\n",
      "201604       4095\n",
      "201603       3143\n",
      "           ...   \n",
      "201111         25\n",
      "201105         24\n",
      "201007         24\n",
      "200908         23\n",
      "201210         22\n",
      "201109         20\n",
      "200912         18\n",
      "200910         16\n",
      "200708         16\n",
      "201202         13\n",
      "201203         12\n",
      "200804         11\n",
      "201112         10\n",
      "201107         10\n",
      "200902         10\n",
      "200511          9\n",
      "200810          8\n",
      "200705          8\n",
      "201009          7\n",
      "201106          6\n",
      "200802          5\n",
      "200609          5\n",
      "201209          5\n",
      "200907          4\n",
      "200806          4\n",
      "200903          3\n",
      "200512          3\n",
      "200906          2\n",
      "201104          2\n",
      "201002          1\n",
      "Name: expiration_date_Ym, Length: 137, dtype: int64\n",
      "\n",
      "genre_ids属性有609的不同取值，各取值及其出现的次数\n",
      "\n",
      "465                      4936899\n",
      "458                      1642162\n",
      "921                       540456\n",
      "1609                      431515\n",
      "444                       334233\n",
      "1259                      244784\n",
      "2022                      207603\n",
      "359                       155309\n",
      "2122                      100316\n",
      "139                        91036\n",
      "451                        90082\n",
      "437                        82698\n",
      "958                        79211\n",
      "786                        58818\n",
      "1616|1609                  51726\n",
      "465|1259                   44650\n",
      "1011                       44343\n",
      "921|465                    40300\n",
      "139|125|109                39628\n",
      "2157                       38065\n",
      "444|1259                   37756\n",
      "726                        31587\n",
      "921|458                    28245\n",
      "947                        21269\n",
      "465|458                    20584\n",
      "1616                       20204\n",
      "786|947                    18580\n",
      "691                        16622\n",
      "1152                       14077\n",
      "829                        13930\n",
      "                          ...   \n",
      "1572|2065                      1\n",
      "1011|359                       1\n",
      "1609|2122|786                  1\n",
      "388|2122                       1\n",
      "1609|275|1572                  1\n",
      "430|691                        1\n",
      "1633|359                       1\n",
      "444|359                        1\n",
      "2122|191                       1\n",
      "1096|958                       1\n",
      "1609|947|726|2022              1\n",
      "1152|465|958                   1\n",
      "109|2022                       1\n",
      "1180|458                       1\n",
      "1089                           1\n",
      "829|822                        1\n",
      "1180|437                       1\n",
      "2122|947|2022                  1\n",
      "1011|2189|367                  1\n",
      "940|726                        1\n",
      "437|139                        1\n",
      "864|850|437|857|843            1\n",
      "2022|1609|139|125|109          1\n",
      "1609|947|2022|958              1\n",
      "109|94                         1\n",
      "465|2130|139                   1\n",
      "921|1633                       1\n",
      "900|958                        1\n",
      "388|958                        1\n",
      "1969|275|2100|1572             1\n",
      "Name: genre_ids, Length: 608, dtype: int64\n",
      "\n",
      "language属性有11的不同取值，各取值及其出现的次数\n",
      "\n",
      " 3.0     5355971\n",
      " 52.0    2573813\n",
      " 31.0     895692\n",
      "-1.0      425608\n",
      " 17.0     329363\n",
      " 10.0     231935\n",
      " 24.0     112872\n",
      " 59.0       5301\n",
      " 45.0       3235\n",
      " 38.0        279\n",
      "Name: language, dtype: int64\n",
      "\n",
      "artist_name属性有46373的不同取值，各取值及其出现的次数\n",
      "\n",
      "Various Artists                             464160\n",
      "周杰倫 (Jay Chou)                              238571\n",
      "五月天 (Mayday)                                221491\n",
      "林俊傑 (JJ Lin)                                146896\n",
      "田馥甄 (Hebe)                                  131881\n",
      "aMEI (張惠妹)                                  104758\n",
      "陳奕迅 (Eason Chan)                             97123\n",
      "玖壹壹                                          87330\n",
      "G.E.M.鄧紫棋                                    83796\n",
      "BIGBANG                                      74941\n",
      "Maroon 5                                     72512\n",
      "謝和弦 (R-chord)                                70254\n",
      "A-Lin                                        70127\n",
      "蔡依林 (Jolin Tsai)                             65472\n",
      "Eric 周興哲                                     59582\n",
      "梁靜茹 (Fish Leong)                             58991\n",
      "楊丞琳 (Rainie Yang)                            58331\n",
      "張學友 (Jacky Cheung)                           58119\n",
      "丁噹 (Della)                                   57859\n",
      "蘇打綠 (Sodagreen)                              57349\n",
      "The Chainsmokers                             55747\n",
      "郭靜 (Claire Kuo)                              55590\n",
      "蕭敬騰 (Jam Hsiao)                              53123\n",
      "吳克群 (Kenji Wu)                               52453\n",
      "林宥嘉 (Yoga Lin)                               51891\n",
      "八三夭 (The Last Day of Summer 831)             50847\n",
      "韋禮安 (William Wei)                            49354\n",
      "范瑋琪 (Christine Fan)                          46084\n",
      "Alan Walker                                  45468\n",
      "李榮浩                                          44202\n",
      "                                             ...  \n",
      "Young Shanty                                     1\n",
      "KoKo| Emeryld                                    1\n",
      "Liquid Stranger                                  1\n",
      "Diamond Eyes| Christina Grimmie                  1\n",
      "Down Town Jazz Band                              1\n",
      "Atomone                                          1\n",
      "雲軒                                               1\n",
      "Bad Boy                                          1\n",
      "ImagineerTracks                                  1\n",
      "Kim Gunmo                                        1\n",
      "Kyle Ferrill  and Roger McVey                    1\n",
      "DJ KM                                            1\n",
      "Hartmut Kiss                                     1\n",
      "hashiyasume (箸休)                                 1\n",
      "Flowjob                                          1\n",
      "Wendy Morrow| Peter Govan| & Clare Evitt         1\n",
      "Vincea McClelland                                1\n",
      "李晉                                               1\n",
      "Jimmy Clanton                                    1\n",
      "Mon Pleang Carabao                               1\n",
      "Listen to the Advertising music easily           1\n",
      "J-AX| The Styles                                 1\n",
      "Acid Ghost                                       1\n",
      "Dave Rawlings Machine                            1\n",
      "James Hall                                       1\n",
      "Cody Bryan Band                                  1\n",
      "金益研二                                             1\n",
      "The Piano Christmas Trio                         1\n",
      "Houndwolf                                        1\n",
      "Kyko                                             1\n",
      "Name: artist_name, Length: 46372, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "cat_features = ['source_screen_name',\n",
    "       'source_system_tab', 'source_type', 'city', 'gender',\n",
    "       'registered_via', 'expiration_date_year', 'expiration_date_month',\n",
    "       'expiration_date_Ym',  'genre_ids', 'language',\n",
    "       'artist_name']\n",
    "for col in cat_features:\n",
    "    num_vlaules = len(data[col].unique())\n",
    "    print('\\n%s属性有%d的不同取值，各取值及其出现的次数\\n'% (col,num_vlaules) )\n",
    "    print(data[col].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 增加特征，表示播放次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "songs_features1 = [ 'genre_ids','artist_name','song_id','msno']\n",
    "for col in songs_features1:\n",
    "    # 创建一个新列表示播放次数\n",
    "    name = str(col+'_counts')\n",
    "    data[name] = np.ones(len(data),'int64')\n",
    "    \n",
    "    data_temp = data[[col,name]]\n",
    "    # 得到播放/点播次数\n",
    "    count = data_temp.groupby(by = col,as_index = False).count()\n",
    "    #删除原来的次数列\n",
    "    data.drop(name, axis=1, inplace = True)\n",
    "    # 融合入data表\n",
    "    data = pd.merge(data, count, how='left', on=col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>...</th>\n",
       "      <th>expiration_date_Ym</th>\n",
       "      <th>use_days</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>language</th>\n",
       "      <th>song_length_s</th>\n",
       "      <th>genre_ids_counts</th>\n",
       "      <th>artist_name_counts</th>\n",
       "      <th>song_id_counts</th>\n",
       "      <th>msno_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>27.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201710</td>\n",
       "      <td>2103</td>\n",
       "      <td>359</td>\n",
       "      <td>Bastille</td>\n",
       "      <td>52.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>155309.0</td>\n",
       "      <td>1432.0</td>\n",
       "      <td>250</td>\n",
       "      <td>7075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201709</td>\n",
       "      <td>2301</td>\n",
       "      <td>1259</td>\n",
       "      <td>Various Artists</td>\n",
       "      <td>52.0</td>\n",
       "      <td>284.0</td>\n",
       "      <td>244784.0</td>\n",
       "      <td>464160.0</td>\n",
       "      <td>1</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201709</td>\n",
       "      <td>2301</td>\n",
       "      <td>1259</td>\n",
       "      <td>Nas</td>\n",
       "      <td>52.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>244784.0</td>\n",
       "      <td>458.0</td>\n",
       "      <td>6</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201709</td>\n",
       "      <td>2301</td>\n",
       "      <td>1019</td>\n",
       "      <td>Soundway</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>255.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>27.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201710</td>\n",
       "      <td>2103</td>\n",
       "      <td>1011</td>\n",
       "      <td>Brett Young</td>\n",
       "      <td>52.0</td>\n",
       "      <td>187.0</td>\n",
       "      <td>44343.0</td>\n",
       "      <td>527.0</td>\n",
       "      <td>474</td>\n",
       "      <td>7075</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                          msno  \\\n",
       "0 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                        song_id source   source_screen_name  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=  train              Explore   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=  train  Local playlist more   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=  train  Local playlist more   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=  train  Local playlist more   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=  train              Explore   \n",
       "\n",
       "  source_system_tab      source_type  target  city    bd  ...  \\\n",
       "0           explore  online-playlist     1.0     1  27.0  ...   \n",
       "1        my library   local-playlist     1.0    13  24.0  ...   \n",
       "2        my library   local-playlist     1.0    13  24.0  ...   \n",
       "3        my library   local-playlist     1.0    13  24.0  ...   \n",
       "4           explore  online-playlist     1.0     1  27.0  ...   \n",
       "\n",
       "  expiration_date_Ym  use_days  genre_ids      artist_name  language  \\\n",
       "0             201710      2103        359         Bastille      52.0   \n",
       "1             201709      2301       1259  Various Artists      52.0   \n",
       "2             201709      2301       1259              Nas      52.0   \n",
       "3             201709      2301       1019         Soundway      -1.0   \n",
       "4             201710      2103       1011      Brett Young      52.0   \n",
       "\n",
       "   song_length_s genre_ids_counts artist_name_counts  song_id_counts  \\\n",
       "0          206.0         155309.0             1432.0             250   \n",
       "1          284.0         244784.0           464160.0               1   \n",
       "2          225.0         244784.0              458.0               6   \n",
       "3          255.0            130.0                1.0               1   \n",
       "4          187.0          44343.0              527.0             474   \n",
       "\n",
       "   msno_counts  \n",
       "0         7075  \n",
       "1          730  \n",
       "2          730  \n",
       "3          730  \n",
       "4         7075  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>target</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>expiration_date_year</th>\n",
       "      <th>expiration_date_month</th>\n",
       "      <th>expiration_date_Ym</th>\n",
       "      <th>use_days</th>\n",
       "      <th>language</th>\n",
       "      <th>song_length_s</th>\n",
       "      <th>genre_ids_counts</th>\n",
       "      <th>artist_name_counts</th>\n",
       "      <th>song_id_counts</th>\n",
       "      <th>msno_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.556790e+06</td>\n",
       "      <td>7.377418e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934069e+06</td>\n",
       "      <td>9.934069e+06</td>\n",
       "      <td>9.773643e+06</td>\n",
       "      <td>9.934069e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "      <td>9.934208e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.278394e+06</td>\n",
       "      <td>5.035171e-01</td>\n",
       "      <td>7.488166e+00</td>\n",
       "      <td>2.805023e+01</td>\n",
       "      <td>6.792155e+00</td>\n",
       "      <td>2.017077e+03</td>\n",
       "      <td>8.326120e+00</td>\n",
       "      <td>2.017160e+05</td>\n",
       "      <td>1.620287e+03</td>\n",
       "      <td>1.895934e+01</td>\n",
       "      <td>2.442900e+02</td>\n",
       "      <td>2.849333e+06</td>\n",
       "      <td>5.475990e+04</td>\n",
       "      <td>1.891112e+03</td>\n",
       "      <td>8.495240e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>7.380818e+05</td>\n",
       "      <td>4.999877e-01</td>\n",
       "      <td>6.648934e+00</td>\n",
       "      <td>6.698503e+00</td>\n",
       "      <td>2.273409e+00</td>\n",
       "      <td>3.898629e-01</td>\n",
       "      <td>2.495316e+00</td>\n",
       "      <td>3.786038e+01</td>\n",
       "      <td>1.133356e+03</td>\n",
       "      <td>2.129982e+01</td>\n",
       "      <td>6.894602e+01</td>\n",
       "      <td>2.162686e+06</td>\n",
       "      <td>1.050230e+05</td>\n",
       "      <td>2.930067e+03</td>\n",
       "      <td>7.737111e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>2.004000e+03</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>2.004100e+05</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6.391972e+05</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>2.600000e+01</td>\n",
       "      <td>4.000000e+00</td>\n",
       "      <td>2.017000e+03</td>\n",
       "      <td>9.000000e+00</td>\n",
       "      <td>2.017090e+05</td>\n",
       "      <td>7.000000e+02</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>2.140000e+02</td>\n",
       "      <td>4.315150e+05</td>\n",
       "      <td>2.500000e+03</td>\n",
       "      <td>8.600000e+01</td>\n",
       "      <td>3.660000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.278394e+06</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>5.000000e+00</td>\n",
       "      <td>2.700000e+01</td>\n",
       "      <td>7.000000e+00</td>\n",
       "      <td>2.017000e+03</td>\n",
       "      <td>9.000000e+00</td>\n",
       "      <td>2.017090e+05</td>\n",
       "      <td>1.430000e+03</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>2.400000e+02</td>\n",
       "      <td>4.936899e+06</td>\n",
       "      <td>1.440000e+04</td>\n",
       "      <td>5.660000e+02</td>\n",
       "      <td>6.560000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.917592e+06</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.300000e+01</td>\n",
       "      <td>2.900000e+01</td>\n",
       "      <td>9.000000e+00</td>\n",
       "      <td>2.017000e+03</td>\n",
       "      <td>1.000000e+01</td>\n",
       "      <td>2.017100e+05</td>\n",
       "      <td>2.284000e+03</td>\n",
       "      <td>5.200000e+01</td>\n",
       "      <td>2.710000e+02</td>\n",
       "      <td>4.936899e+06</td>\n",
       "      <td>5.245300e+04</td>\n",
       "      <td>2.228000e+03</td>\n",
       "      <td>1.087000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.556789e+06</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>2.200000e+01</td>\n",
       "      <td>8.700000e+01</td>\n",
       "      <td>1.600000e+01</td>\n",
       "      <td>2.020000e+03</td>\n",
       "      <td>1.200000e+01</td>\n",
       "      <td>2.020100e+05</td>\n",
       "      <td>5.149000e+03</td>\n",
       "      <td>5.900000e+01</td>\n",
       "      <td>1.085100e+04</td>\n",
       "      <td>4.936899e+06</td>\n",
       "      <td>4.641600e+05</td>\n",
       "      <td>1.601900e+04</td>\n",
       "      <td>7.894000e+03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id        target          city            bd  registered_via  \\\n",
       "count  2.556790e+06  7.377418e+06  9.934208e+06  9.934208e+06    9.934208e+06   \n",
       "mean   1.278394e+06  5.035171e-01  7.488166e+00  2.805023e+01    6.792155e+00   \n",
       "std    7.380818e+05  4.999877e-01  6.648934e+00  6.698503e+00    2.273409e+00   \n",
       "min    0.000000e+00  0.000000e+00  1.000000e+00  3.000000e+00    3.000000e+00   \n",
       "25%    6.391972e+05  0.000000e+00  1.000000e+00  2.600000e+01    4.000000e+00   \n",
       "50%    1.278394e+06  1.000000e+00  5.000000e+00  2.700000e+01    7.000000e+00   \n",
       "75%    1.917592e+06  1.000000e+00  1.300000e+01  2.900000e+01    9.000000e+00   \n",
       "max    2.556789e+06  1.000000e+00  2.200000e+01  8.700000e+01    1.600000e+01   \n",
       "\n",
       "       expiration_date_year  expiration_date_month  expiration_date_Ym  \\\n",
       "count          9.934208e+06           9.934208e+06        9.934208e+06   \n",
       "mean           2.017077e+03           8.326120e+00        2.017160e+05   \n",
       "std            3.898629e-01           2.495316e+00        3.786038e+01   \n",
       "min            2.004000e+03           1.000000e+00        2.004100e+05   \n",
       "25%            2.017000e+03           9.000000e+00        2.017090e+05   \n",
       "50%            2.017000e+03           9.000000e+00        2.017090e+05   \n",
       "75%            2.017000e+03           1.000000e+01        2.017100e+05   \n",
       "max            2.020000e+03           1.200000e+01        2.020100e+05   \n",
       "\n",
       "           use_days      language  song_length_s  genre_ids_counts  \\\n",
       "count  9.934208e+06  9.934069e+06   9.934069e+06      9.773643e+06   \n",
       "mean   1.620287e+03  1.895934e+01   2.442900e+02      2.849333e+06   \n",
       "std    1.133356e+03  2.129982e+01   6.894602e+01      2.162686e+06   \n",
       "min    0.000000e+00 -1.000000e+00   1.000000e+00      1.000000e+00   \n",
       "25%    7.000000e+02  3.000000e+00   2.140000e+02      4.315150e+05   \n",
       "50%    1.430000e+03  3.000000e+00   2.400000e+02      4.936899e+06   \n",
       "75%    2.284000e+03  5.200000e+01   2.710000e+02      4.936899e+06   \n",
       "max    5.149000e+03  5.900000e+01   1.085100e+04      4.936899e+06   \n",
       "\n",
       "       artist_name_counts  song_id_counts   msno_counts  \n",
       "count        9.934069e+06    9.934208e+06  9.934208e+06  \n",
       "mean         5.475990e+04    1.891112e+03  8.495240e+02  \n",
       "std          1.050230e+05    2.930067e+03  7.737111e+02  \n",
       "min          1.000000e+00    1.000000e+00  1.000000e+00  \n",
       "25%          2.500000e+03    8.600000e+01  3.660000e+02  \n",
       "50%          1.440000e+04    5.660000e+02  6.560000e+02  \n",
       "75%          5.245300e+04    2.228000e+03  1.087000e+03  \n",
       "max          4.641600e+05    1.601900e+04  7.894000e+03  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                       7377418\n",
       "msno                           0\n",
       "song_id                        0\n",
       "source                         0\n",
       "source_screen_name        577687\n",
       "source_system_tab          33291\n",
       "source_type                28836\n",
       "target                   2556790\n",
       "city                           0\n",
       "bd                             0\n",
       "gender                         0\n",
       "registered_via                 0\n",
       "expiration_date_year           0\n",
       "expiration_date_month          0\n",
       "expiration_date_Ym             0\n",
       "use_days                       0\n",
       "genre_ids                 160565\n",
       "artist_name                  139\n",
       "language                     139\n",
       "song_length_s                139\n",
       "genre_ids_counts          160565\n",
       "artist_name_counts           139\n",
       "song_id_counts                 0\n",
       "msno_counts                    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "genre_ids 609\n",
    "artist_name 46373\n",
    "## 类别特征编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>artist_name_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>9.934069e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.475990e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.050230e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.500000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.440000e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>5.245300e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4.641600e+05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       artist_name_counts\n",
       "count        9.934069e+06\n",
       "mean         5.475990e+04\n",
       "std          1.050230e+05\n",
       "min          1.000000e+00\n",
       "25%          2.500000e+03\n",
       "50%          1.440000e+04\n",
       "75%          5.245300e+04\n",
       "max          4.641600e+05"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[['genre_ids','artist_name_counts']].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "songs_features2 = ['genre_ids','artist_name']\n",
    "for col in songs_features2:\n",
    "    \n",
    "    '''\n",
    "    value_counts_col = data[col].value_counts()\n",
    "    # 可通过逐步调参确定阈值\n",
    "    rare_threshold = 100\n",
    "    value_counts_rare = list(value_counts_col[value_counts_col < rare_threshold ].index)\n",
    "    \n",
    "    rare_index = data[col].isin(value_counts_rare)\n",
    "    data.loc[ data[col].isin(value_counts_rare), col] = 'Others'\n",
    "    '''\n",
    "    #可调阈值将过多的类别值归一\n",
    "    data[col].fillna('Others',inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "genre_ids 221\n",
      "artist_name 4831\n"
     ]
    }
   ],
   "source": [
    "for col in songs_features2:\n",
    "    print(col,len(data[col].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Various Artists                       464160\n",
       "Others                                430487\n",
       "周杰倫 (Jay Chou)                        238571\n",
       "五月天 (Mayday)                          221491\n",
       "林俊傑 (JJ Lin)                          146896\n",
       "田馥甄 (Hebe)                            131881\n",
       "aMEI (張惠妹)                            104758\n",
       "陳奕迅 (Eason Chan)                       97123\n",
       "玖壹壹                                    87330\n",
       "G.E.M.鄧紫棋                              83796\n",
       "BIGBANG                                74941\n",
       "Maroon 5                               72512\n",
       "謝和弦 (R-chord)                          70254\n",
       "A-Lin                                  70127\n",
       "蔡依林 (Jolin Tsai)                       65472\n",
       "Eric 周興哲                               59582\n",
       "梁靜茹 (Fish Leong)                       58991\n",
       "楊丞琳 (Rainie Yang)                      58331\n",
       "張學友 (Jacky Cheung)                     58119\n",
       "丁噹 (Della)                             57859\n",
       "蘇打綠 (Sodagreen)                        57349\n",
       "The Chainsmokers                       55747\n",
       "郭靜 (Claire Kuo)                        55590\n",
       "蕭敬騰 (Jam Hsiao)                        53123\n",
       "吳克群 (Kenji Wu)                         52453\n",
       "林宥嘉 (Yoga Lin)                         51891\n",
       "八三夭 (The Last Day of Summer 831)       50847\n",
       "韋禮安 (William Wei)                      49354\n",
       "范瑋琪 (Christine Fan)                    46084\n",
       "Alan Walker                            45468\n",
       "                                       ...  \n",
       "Chakra 脉轮                                101\n",
       "葉樹涵                                      101\n",
       "佛教閩南語課誦系列                                101\n",
       "Verditune                                101\n",
       "Molotov Cocktail Piano                   101\n",
       "CHARLY BLACK                             101\n",
       "戀愛占星音樂全精選                                101\n",
       "Foy Vance                                100\n",
       "Jeanne Lamon| Tafelmusik Orchestra       100\n",
       "NGHTMRE                                  100\n",
       "Emmanuel Pahud                           100\n",
       "Oguro Maki                               100\n",
       "薛岳                                       100\n",
       "Kangto Band                              100\n",
       "Dario Marianelli| Regina Spektor         100\n",
       "Aoi Yuuki (悠木 碧)                         100\n",
       "Shin Hyesung and LYn                     100\n",
       "Quil Tanachen                            100\n",
       "N.Flying                                 100\n",
       "Melotonics                               100\n",
       "Paolo Bordoni                            100\n",
       "哈韓瘋日必備歌                                  100\n",
       "花兒 (The Flowers)                         100\n",
       "Ben Webster                              100\n",
       "Jang Hee Young| Yoo Se Yun               100\n",
       "The Devil Wears Prada Soundtrack         100\n",
       "陳奐仁 (Hanjin Tan)                         100\n",
       "陳昱熙                                      100\n",
       "GB9                                      100\n",
       "胡鴻鈞 (Hubert Wu)                          100\n",
       "Name: artist_name, Length: 4831, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.artist_name.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>...</th>\n",
       "      <th>expiration_date_Ym</th>\n",
       "      <th>use_days</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>language</th>\n",
       "      <th>song_length_s</th>\n",
       "      <th>genre_ids_counts</th>\n",
       "      <th>artist_name_counts</th>\n",
       "      <th>song_id_counts</th>\n",
       "      <th>msno_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>27.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201710</td>\n",
       "      <td>2103</td>\n",
       "      <td>359</td>\n",
       "      <td>Bastille</td>\n",
       "      <td>52.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>155309.0</td>\n",
       "      <td>1432.0</td>\n",
       "      <td>250</td>\n",
       "      <td>7075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201709</td>\n",
       "      <td>2301</td>\n",
       "      <td>1259</td>\n",
       "      <td>Various Artists</td>\n",
       "      <td>52.0</td>\n",
       "      <td>284.0</td>\n",
       "      <td>244784.0</td>\n",
       "      <td>464160.0</td>\n",
       "      <td>1</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201709</td>\n",
       "      <td>2301</td>\n",
       "      <td>1259</td>\n",
       "      <td>Nas</td>\n",
       "      <td>52.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>244784.0</td>\n",
       "      <td>458.0</td>\n",
       "      <td>6</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>train</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>my library</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201709</td>\n",
       "      <td>2301</td>\n",
       "      <td>1019</td>\n",
       "      <td>Others</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>255.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>train</td>\n",
       "      <td>Explore</td>\n",
       "      <td>explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>27.0</td>\n",
       "      <td>...</td>\n",
       "      <td>201710</td>\n",
       "      <td>2103</td>\n",
       "      <td>1011</td>\n",
       "      <td>Brett Young</td>\n",
       "      <td>52.0</td>\n",
       "      <td>187.0</td>\n",
       "      <td>44343.0</td>\n",
       "      <td>527.0</td>\n",
       "      <td>474</td>\n",
       "      <td>7075</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                          msno  \\\n",
       "0 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3 NaN  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4 NaN  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                        song_id source   source_screen_name  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=  train              Explore   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=  train  Local playlist more   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=  train  Local playlist more   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=  train  Local playlist more   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=  train              Explore   \n",
       "\n",
       "  source_system_tab      source_type  target  city    bd  ...  \\\n",
       "0           explore  online-playlist     1.0     1  27.0  ...   \n",
       "1        my library   local-playlist     1.0    13  24.0  ...   \n",
       "2        my library   local-playlist     1.0    13  24.0  ...   \n",
       "3        my library   local-playlist     1.0    13  24.0  ...   \n",
       "4           explore  online-playlist     1.0     1  27.0  ...   \n",
       "\n",
       "  expiration_date_Ym  use_days  genre_ids      artist_name  language  \\\n",
       "0             201710      2103        359         Bastille      52.0   \n",
       "1             201709      2301       1259  Various Artists      52.0   \n",
       "2             201709      2301       1259              Nas      52.0   \n",
       "3             201709      2301       1019           Others      -1.0   \n",
       "4             201710      2103       1011      Brett Young      52.0   \n",
       "\n",
       "   song_length_s genre_ids_counts artist_name_counts  song_id_counts  \\\n",
       "0          206.0         155309.0             1432.0             250   \n",
       "1          284.0         244784.0           464160.0               1   \n",
       "2          225.0         244784.0              458.0               6   \n",
       "3          255.0            130.0                1.0               1   \n",
       "4          187.0          44343.0              527.0             474   \n",
       "\n",
       "   msno_counts  \n",
       "0         7075  \n",
       "1          730  \n",
       "2          730  \n",
       "3          730  \n",
       "4         7075  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 9934208 entries, 0 to 9934207\n",
      "Data columns (total 24 columns):\n",
      "id                       float64\n",
      "msno                     object\n",
      "song_id                  object\n",
      "source                   object\n",
      "source_screen_name       object\n",
      "source_system_tab        object\n",
      "source_type              object\n",
      "target                   float64\n",
      "city                     int64\n",
      "bd                       float64\n",
      "gender                   object\n",
      "registered_via           int64\n",
      "expiration_date_year     int64\n",
      "expiration_date_month    int64\n",
      "expiration_date_Ym       int64\n",
      "use_days                 int64\n",
      "genre_ids                object\n",
      "artist_name              object\n",
      "language                 float64\n",
      "song_length_s            float64\n",
      "genre_ids_counts         float64\n",
      "artist_name_counts       float64\n",
      "song_id_counts           int64\n",
      "msno_counts              int64\n",
      "dtypes: float64(7), int64(8), object(9)\n",
      "memory usage: 1.9+ GB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id 2556791\n",
      "msno 34403\n",
      "song_id 419839\n",
      "source 2\n",
      "source_screen_name 23\n",
      "source_system_tab 9\n",
      "source_type 13\n",
      "target 3\n",
      "city 21\n",
      "bd 72\n",
      "gender 3\n",
      "registered_via 6\n",
      "expiration_date_year 17\n",
      "expiration_date_month 12\n",
      "expiration_date_Ym 137\n",
      "use_days 4345\n",
      "genre_ids 221\n",
      "artist_name 4831\n",
      "language 11\n",
      "song_length_s 1926\n",
      "genre_ids_counts 279\n",
      "artist_name_counts 1856\n",
      "song_id_counts 2121\n",
      "msno_counts 1933\n"
     ]
    }
   ],
   "source": [
    "for col in data.columns:\n",
    "    print(col,len(data[col].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'msno', 'song_id', 'source', 'source_screen_name',\n",
       "       'source_system_tab', 'source_type', 'target', 'city', 'bd', 'gender',\n",
       "       'registered_via', 'expiration_date_year', 'expiration_date_month',\n",
       "       'expiration_date_Ym', 'use_days', 'genre_ids', 'artist_name',\n",
       "       'language', 'song_length_s', 'genre_ids_counts', 'artist_name_counts',\n",
       "       'song_id_counts', 'msno_counts'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "feats_to_encode = ['source_screen_name',\n",
    "       'source_system_tab', 'source_type','city', 'gender',\n",
    "       'registered_via', 'expiration_date_year', 'expiration_date_month',\n",
    "       'expiration_date_Ym', 'genre_ids', 'artist_name',\n",
    "       'language']\n",
    "\n",
    "for col in feats_to_encode:\n",
    "    data[col] = data[col].astype(object)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "source_screen_name       577687\n",
       "source_system_tab         33291\n",
       "source_type               28836\n",
       "city                          0\n",
       "gender                        0\n",
       "registered_via                0\n",
       "expiration_date_year          0\n",
       "expiration_date_month         0\n",
       "expiration_date_Ym            0\n",
       "genre_ids                     0\n",
       "artist_name                   0\n",
       "language                    139\n",
       "dtype: int64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[feats_to_encode].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['language'].fillna(0,inplace = True)\n",
    "data['source_screen_name'].fillna('unknown',inplace = True)\n",
    "data[ 'source_system_tab'].fillna('unknown',inplace = True)\n",
    "data[ 'source_type'].fillna('unknown',inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import LabelEncoder\n",
    "le = LabelEncoder()\n",
    "\n",
    "for col in feats_to_encode:\n",
    "    data[col] = le.fit_transform(data[col])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = data.loc[data['source']=='train']\n",
    "test = data.loc[data['source']=='test']\n",
    "train.drop(['source','id'],axis=1,inplace=True)\n",
    "test.drop(['source','target'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>...</th>\n",
       "      <th>expiration_date_Ym</th>\n",
       "      <th>use_days</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>language</th>\n",
       "      <th>song_length_s</th>\n",
       "      <th>genre_ids_counts</th>\n",
       "      <th>artist_name_counts</th>\n",
       "      <th>song_id_counts</th>\n",
       "      <th>msno_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>111</td>\n",
       "      <td>2103</td>\n",
       "      <td>101</td>\n",
       "      <td>346</td>\n",
       "      <td>9</td>\n",
       "      <td>206.0</td>\n",
       "      <td>155309.0</td>\n",
       "      <td>1432.0</td>\n",
       "      <td>250</td>\n",
       "      <td>7075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>110</td>\n",
       "      <td>2301</td>\n",
       "      <td>27</td>\n",
       "      <td>3029</td>\n",
       "      <td>9</td>\n",
       "      <td>284.0</td>\n",
       "      <td>244784.0</td>\n",
       "      <td>464160.0</td>\n",
       "      <td>1</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>110</td>\n",
       "      <td>2301</td>\n",
       "      <td>27</td>\n",
       "      <td>2060</td>\n",
       "      <td>9</td>\n",
       "      <td>225.0</td>\n",
       "      <td>244784.0</td>\n",
       "      <td>458.0</td>\n",
       "      <td>6</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>110</td>\n",
       "      <td>2301</td>\n",
       "      <td>1</td>\n",
       "      <td>2164</td>\n",
       "      <td>0</td>\n",
       "      <td>255.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>111</td>\n",
       "      <td>2103</td>\n",
       "      <td>0</td>\n",
       "      <td>444</td>\n",
       "      <td>9</td>\n",
       "      <td>187.0</td>\n",
       "      <td>44343.0</td>\n",
       "      <td>527.0</td>\n",
       "      <td>474</td>\n",
       "      <td>7075</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  \\\n",
       "0  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                        song_id  source_screen_name  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=                   7   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=                   8   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=                   8   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=                   8   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=                   7   \n",
       "\n",
       "   source_system_tab  source_type  target  city    bd  gender  registered_via  \\\n",
       "0                  1            6     1.0     0  27.0       2               2   \n",
       "1                  3            4     1.0    11  24.0       0               3   \n",
       "2                  3            4     1.0    11  24.0       0               3   \n",
       "3                  3            4     1.0    11  24.0       0               3   \n",
       "4                  1            6     1.0     0  27.0       2               2   \n",
       "\n",
       "   ...  expiration_date_Ym  use_days  genre_ids  artist_name  language  \\\n",
       "0  ...                 111      2103        101          346         9   \n",
       "1  ...                 110      2301         27         3029         9   \n",
       "2  ...                 110      2301         27         2060         9   \n",
       "3  ...                 110      2301          1         2164         0   \n",
       "4  ...                 111      2103          0          444         9   \n",
       "\n",
       "   song_length_s  genre_ids_counts  artist_name_counts  song_id_counts  \\\n",
       "0          206.0          155309.0              1432.0             250   \n",
       "1          284.0          244784.0            464160.0               1   \n",
       "2          225.0          244784.0               458.0               6   \n",
       "3          255.0             130.0                 1.0               1   \n",
       "4          187.0           44343.0               527.0             474   \n",
       "\n",
       "   msno_counts  \n",
       "0         7075  \n",
       "1          730  \n",
       "2          730  \n",
       "3          730  \n",
       "4         7075  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_type</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>...</th>\n",
       "      <th>expiration_date_Ym</th>\n",
       "      <th>use_days</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>language</th>\n",
       "      <th>song_length_s</th>\n",
       "      <th>genre_ids_counts</th>\n",
       "      <th>artist_name_counts</th>\n",
       "      <th>song_id_counts</th>\n",
       "      <th>msno_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7377418</th>\n",
       "      <td>0.0</td>\n",
       "      <td>V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=</td>\n",
       "      <td>WmHKgKMlp1lQMecNdNvDMkvIycZYHnFwDT72I5sIssc=</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>110</td>\n",
       "      <td>577</td>\n",
       "      <td>125</td>\n",
       "      <td>3980</td>\n",
       "      <td>2</td>\n",
       "      <td>224.0</td>\n",
       "      <td>1642162.0</td>\n",
       "      <td>17663.0</td>\n",
       "      <td>890</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377419</th>\n",
       "      <td>1.0</td>\n",
       "      <td>V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=</td>\n",
       "      <td>y/rsZ9DC7FwK5F2PK2D5mj+aOBUJAjuu3dZ14NgE0vM=</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>110</td>\n",
       "      <td>577</td>\n",
       "      <td>128</td>\n",
       "      <td>3927</td>\n",
       "      <td>2</td>\n",
       "      <td>320.0</td>\n",
       "      <td>4936899.0</td>\n",
       "      <td>146896.0</td>\n",
       "      <td>7569</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377420</th>\n",
       "      <td>2.0</td>\n",
       "      <td>/uQAlrAkaczV+nWCd2sPF2ekvXPRipV7q0l+gbLuxjw=</td>\n",
       "      <td>8eZLFOdGVdXBSqoAv5nsLigeH2BvKXzTQYtUM53I0k4=</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>7</td>\n",
       "      <td>62</td>\n",
       "      <td>3151</td>\n",
       "      <td>4</td>\n",
       "      <td>315.0</td>\n",
       "      <td>207603.0</td>\n",
       "      <td>1138.0</td>\n",
       "      <td>7</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377421</th>\n",
       "      <td>3.0</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>ztCf8thYsS4YN3GcIL/bvoxLm/T5mYBVKOO4C9NiVfQ=</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>105</td>\n",
       "      <td>3567</td>\n",
       "      <td>128</td>\n",
       "      <td>2977</td>\n",
       "      <td>9</td>\n",
       "      <td>285.0</td>\n",
       "      <td>4936899.0</td>\n",
       "      <td>923.0</td>\n",
       "      <td>45</td>\n",
       "      <td>658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377422</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>MKVMpslKcQhMaFEgcEQhEfi5+RZhMYlU3eRDpySrH8Y=</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>105</td>\n",
       "      <td>3567</td>\n",
       "      <td>186</td>\n",
       "      <td>3136</td>\n",
       "      <td>0</td>\n",
       "      <td>197.0</td>\n",
       "      <td>9200.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>8</td>\n",
       "      <td>658</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                          msno  \\\n",
       "7377418  0.0  V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=   \n",
       "7377419  1.0  V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=   \n",
       "7377420  2.0  /uQAlrAkaczV+nWCd2sPF2ekvXPRipV7q0l+gbLuxjw=   \n",
       "7377421  3.0  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "7377422  4.0  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "\n",
       "                                              song_id  source_screen_name  \\\n",
       "7377418  WmHKgKMlp1lQMecNdNvDMkvIycZYHnFwDT72I5sIssc=                   8   \n",
       "7377419  y/rsZ9DC7FwK5F2PK2D5mj+aOBUJAjuu3dZ14NgE0vM=                   8   \n",
       "7377420  8eZLFOdGVdXBSqoAv5nsLigeH2BvKXzTQYtUM53I0k4=                  22   \n",
       "7377421  ztCf8thYsS4YN3GcIL/bvoxLm/T5mYBVKOO4C9NiVfQ=                  16   \n",
       "7377422  MKVMpslKcQhMaFEgcEQhEfi5+RZhMYlU3eRDpySrH8Y=                  16   \n",
       "\n",
       "         source_system_tab  source_type  city    bd  gender  registered_via  \\\n",
       "7377418                  3            3     0  27.0       2               2   \n",
       "7377419                  3            3     0  27.0       2               2   \n",
       "7377420                  0            9     0  27.0       2               1   \n",
       "7377421                  5            7     1  30.0       1               3   \n",
       "7377422                  5            7     1  30.0       1               3   \n",
       "\n",
       "         ...  expiration_date_Ym  use_days  genre_ids  artist_name  language  \\\n",
       "7377418  ...                 110       577        125         3980         2   \n",
       "7377419  ...                 110       577        128         3927         2   \n",
       "7377420  ...                 100         7         62         3151         4   \n",
       "7377421  ...                 105      3567        128         2977         9   \n",
       "7377422  ...                 105      3567        186         3136         0   \n",
       "\n",
       "         song_length_s  genre_ids_counts  artist_name_counts  song_id_counts  \\\n",
       "7377418          224.0         1642162.0             17663.0             890   \n",
       "7377419          320.0         4936899.0            146896.0            7569   \n",
       "7377420          315.0          207603.0              1138.0               7   \n",
       "7377421          285.0         4936899.0               923.0              45   \n",
       "7377422          197.0            9200.0               260.0               8   \n",
       "\n",
       "         msno_counts  \n",
       "7377418          159  \n",
       "7377419          159  \n",
       "7377420          133  \n",
       "7377421          658  \n",
       "7377422          658  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.to_csv('FE_train_LGBM.csv',index = False)\n",
    "test.to_csv('FE_test_LGBM.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9934208, 24)"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
